
/**
 * File Comment: lots of editing
 */
// $codepro.audit.disable com.instantiations.assist.eclipse.analysis.unusedReturnValue
// $codepro.audit.disable com.instantiations.assist.eclipse.analysis.logForging
// $codepro.audit.disable nullPointerDereference
// $codepro.audit.disable closeInFinally
// $codepro.audit.disable numericLiterals

package edu.gatech.oad.project.externalcommunication;

import java.sql.Connection; 
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import edu.gatech.oad.project.coordinator.Office;
import edu.gatech.oad.project.operations.data.Appointment;
import edu.gatech.oad.project.operations.data.AppointmentStatus;
import edu.gatech.oad.project.operations.data.DoctorOrders;
import edu.gatech.oad.project.operations.data.PatientInformation;
import edu.gatech.oad.project.operations.data.TreatmentRecord;
import edu.gatech.oad.project.operations.users.Doctor;
import edu.gatech.oad.project.operations.users.Nurse;
import edu.gatech.oad.project.operations.users.Patient;
import edu.gatech.oad.project.operations.users.UserCache;

/**
 * IOConverter does all of the external communication for all classes.
 * 
 * @author Group 17
 * @version 1.0
 */
public class IOConverter {
    
    /**
     * Field myLogger: holds the logger for the io file
     */
    private static final Logger MYLOGGER = Logger.getLogger("edu.gatech." +
            "oad.project.externalcommunication");
    
    /**
     * Field con: connection to the database
     */
    private Connection con;
    
    /**
     * Field cache: users
     */
    private UserCache cache;
    
    /**
     * Simply creates a wrapper to connect to the database.
     */
    public IOConverter() {
        
        con = null;
        cache = null;
        
        MYLOGGER.addHandler(Office.FILEHANDLER);
        MYLOGGER.setLevel(Level.ALL);
        MYLOGGER.finest("IOConverter created.");
    }
  
    /**
     * Sets the user cache
     * 
     * @param cache the cache to set
     */
    public void setCache(UserCache cache) {
        this.cache = cache;
    }
    
    //==============================================================================   
    //=====================CONNECTION AND EXECUTION WRAPPERS========================
    //==============================================================================
    /**
     * Connects to the main database. Will return null
     * and log the exception of the connection fails.
     * Only one connection can be made at a time to
     * prevent possible complication of thread changing.
     * 
     * @return the connection
     */
    private Connection connect() {
        
        try {
            final String url = "jdbc:mysql://academic-mysql.cc.gatech.edu/cs2340_group17";
            con = DriverManager.getConnection(url, "cs2340_group17", "qrNTQF_v");
            
            if(!con.isClosed()){ 
                MYLOGGER.info("Successfully connected to " + 
                                "MySQL server using TCP/IP...");
            }
        }
        catch(Exception e){
            MYLOGGER.severe("Exception: " + e.getMessage());
        }

        return con;
    }
    
    /**
     * Closes the personal connection to the database.
     */
    private void close(){
       
        if (this.con != null) {
            try {
                this.con.close(); 
                     
                if(this.con.isClosed()){
                    MYLOGGER.info("Successfully closed connection");
                }
                this.con = null;
            }
            catch(SQLException e) {
                MYLOGGER.severe("Exception: " + e.getMessage());
            }
        }
    }
    
    /**
     * Executes a statement given by the query
     * 
     * @param query the sql to execute
     * @return raw result of sql
     */
    public ResultSet execute(String query) {
        ResultSet answer = null;
        this.connect();
        try {
            final Statement command = this.con.createStatement();
            command.execute(query);
            answer = command.getResultSet();
            //command.close();
            
        }
        catch (SQLException e) {
            MYLOGGER.severe("error in sql statement: " + e.getMessage());
        }
        
        return answer;
    }
    
    /**
     * Executes several statements given by the query
     * 
     * @param queries the sql statements to execute
     * @return the results
     */
    private ResultSet[] executeAll(String[] queries) {
        final ResultSet[] answer = new ResultSet[queries.length];
        Statement command;
        boolean cont;
        this.connect();
        try {
            for(int i = 0; i < queries.length; i++) {
                command = this.con.createStatement();
                cont = command.execute(queries[i]);
                if (cont){
                    answer[i] = command.getResultSet();
                }
                else {
                    answer[i] = null;
                }
                //command.close();
            }
        }
        catch (SQLException e) {
            MYLOGGER.severe("error in sql statement: " + e.getMessage());
        }
        
        return answer;
    }
    //==============================================================================   
    //================================END WRAPPERS==================================
    //==============================================================================
  
    //==============================================================================   
    //=================================CRUD USERS===================================
    //==============================================================================
    /**
     * Retrieves all the doctors from the database
     * 
     * @return the doctors from the database
     */
    public Doctor[] getAllDoctors() {
        
        final ResultSet rawID = this.execute("select id from users where " +
        		"rank = 'doctor' and active = 1");
        
        try {
            final List<Integer> ids = new ArrayList<Integer>(); 
            while(rawID.next()) {
                ids.add(rawID.getInt("id"));
            }
            final Doctor[] doctors = new Doctor[ids.size()];
            for (int i = 0; i < ids.size(); i++) {
                doctors[i] = this.getDoctorByID(ids.get(i));
            }
            this.close();
            return doctors;
            
        } catch (SQLException e) {
            MYLOGGER.severe("" + e);
        }
        this.close();     
        return null; // $codepro.audit.disable returnValue
       
    }
    
    /**
     * Retrieves a doctor based on id
     * 
     * @param id the id
     * @return the doctor class
     */
    public Doctor getDoctorByID(int id) {
        return getDoctor(" where id = " + Integer.toString(id) + ";");
    }

    /**
     * Gets the doctor based on the where clause passed in
     * 
     * @param where sql statement to determine which doctor
     * @return a single doctor
     */
    private Doctor getDoctor(String where) {
        final String query = "select * from doctors" + where;
        final ResultSet raw = this.execute(query);
        
        try {
            if(raw.next()) {
                String first = raw.getString("firstName");
                String middle = raw.getString("middleName");
                String last = raw.getString("lastName");
                
                if(first == null) {
                    first = "";
                }
                if(middle == null) {
                    middle = "";
                }
                if(last == null) {
                    last = "";
                }
                final String name = first + " " + middle + " " + last;
                final int id = raw.getInt("id");        
                return new Doctor(name, id);
            }
        } catch (SQLException e) {
            MYLOGGER.severe("" + e);
        }
        return null;
    }

    /**
     * Generates the query to add a patient to the database
     * 
     * @param pat the new patient
     */
    public void addPatient(Patient pat) { // $codepro.audit.disable cyclomaticComplexity
        
        final String name = pat.getName();
        String first = "NULL";
        String middle = "NULL";
        String last = "NULL";
        
        if (name != null) {
            final String[] names = name.split(" ");
            if (names.length >= 3) {
                first = "'" + names[0] + "'";
                last = "'" + names[-1] + "'";
                //middle = "'";
                final StringBuffer middleBuff = new StringBuffer();
                middleBuff.append('"');
                for (int i = 1; i < names.length; i++) {
                    middleBuff.append(names[i]);
                }
                middleBuff.append('"');
                middle = middleBuff.toString();
            }
            else if (names.length >= 2) {
                first = "'" + names[0] + "'";
                last = "'" + names[-1] + "'";
            }
            else if (names.length >= 1){
                first = "'" + names[0] + "'";
            }
        }
        
        final int id = pat.getID();
        final PatientInformation info = pat.getInformation();
        
        String address = "NULL";
        String email = "NULL";
        String gender = "NULL";
        String age = "NULL";
        String pharmacy = "NULL";
        String insurance = "NULL";
        final List<String> rawQuer = new ArrayList<String>();
        
        if (info != null){
            final List<Integer> numbers = info.getPhoneNumbers();
            for(int i = 0; i < numbers.size(); i++) {
                rawQuer.add("insert into phoneNumbers (id, data) values ("
                        + Integer.toString(id) + ", " + Integer.toString(numbers.get(i))
                        + ");");
            }
            
            final List<String> miscPers = info.getMiscPersonalData();
            for(int i = 0; i < miscPers.size(); i++) {
                rawQuer.add("insert into miscData (id, data) values ("
                        + Integer.toString(id) + ", '" + miscPers.get(i)
                        + "');");
            }
            
            final List<String> allergies = info.getAllergies();
            for(int i = 0; i < allergies.size(); i++) {
                rawQuer.add("insert into allergies (id, data) values ("
                        + Integer.toString(id) + ", '" + allergies.get(i)
                        + "');");
            }
            
            final List<String> miscMed = info.getMiscMedicalData();
            for(int i = 0; i < miscMed.size(); i++) {
                rawQuer.add("insert into medicalData (id, data) values ("
                        + Integer.toString(id) + ", '" + miscMed.get(i)
                        + "');");
            }
            
            if (info.getAddress() != null) {
                address = "'" + info.getAddress() + "'";
            }
            if (info.getEmail() != null) {
                email = "'" + info.getEmail() + "'";
            }
            if (info.getGender() != null) {
                gender = "'" + info.getStringGender() + "'";
            }
            if (info.getAge() != 0) {
                age = Integer.toString(info.getAge());
            }
            if (info.getPharmacy() != null){
                pharmacy = "'" + info.getPharmacy() + "'";
            }
            
            if (info.getInsuranceCarrier() != null) {
                insurance = "'" + info.getInsuranceCarrier() + "'"; 
            }

        }
        final int offset = 1;
        final String[] queries = new String[offset + rawQuer.size()];
        queries[0] = "insert into patients values(" + Integer.toString(id)
                     + ", " + first + ", " + middle + ", " + last + ", "
                     + address + ", " + email + ", " + gender + ", "
                     + age + ", " + pharmacy + ", " + insurance + ");";
        for(int i = 0; i < rawQuer.size(); i++) {
            queries[i + 1] = rawQuer.get(i);
        }
        this.executeAll(queries);
        this.close();     
    }
    //==============================================================================   
    //=============================END CRUD USERS===================================
    //==============================================================================
    
    //==============================================================================   
    //===========CRUD APPOINTMENTS AND R TREATMENT RECORDS/DOCTORS ORDERS===========
    //==============================================================================
    /**
     * Generates the add appointment command and returns
     * the randomly generated id
     * 
     * @param app the appointment to add in the database
     * @return the id of the appointment
     */
    public int addAppointment(Appointment app) {
        String time = Integer.toString(app.getTime());
        if (app.getTime() < 1000) {
            time = "0" + time;
        }

        String date = Integer.toString(app.getDate()) + time;
        date = date.substring(0, 4) + '-' + date.substring(4, 6) 
                                    + '-' + date.substring(6, 8)
                                    + ' ' + date.substring(8, 10) 
                                    + ":" + date.substring(10, 12) 
                                    + ":00";

        String vals = " values(" + Integer.toString(app.getPat().getID()) + ", "
                        + Integer.toString(app.getDoc().getID()) + ", '"
                        + date + "', '" + app.getReason() + "', '" 
                        + app.getStatus() + "'";
        
        String cols = " (p_id, d_id, time, reason, status";
        String query = "insert into appointments";
        
        final Nurse nurse = app.getRecord().getAttendingNurse();
        if (null != nurse) {
            vals = vals + ", " + Integer.toString(nurse.getID());
            cols = cols + ", n_id";
        }
        final String diag = app.getRecord().getDiagnosis();
        if (null != diag) {
            vals = vals + ", '" + diag + "'";
            cols = cols + ", diagnosis";
        }
        vals = vals + ")";
        cols = cols + ")";
        query = query + cols + vals + ";";
        
        final ResultSet[] rawdata = this.executeAll(new String[] {query,
                "select last_insert_id() as id;"});
        int id;
        try {
            rawdata[1].next();
            id = rawdata[1].getInt("id");
        } 
        catch (Exception e) {
            MYLOGGER.severe("Exception: " + e.getMessage());
            id = -1;
        }
        finally {
            this.close();
        }
        //this.addTreatmentRecord(app.getRecord(), id);
        return id;
        
    }
    
    /**
     * Gets a single appointment based on id
     * 
     * @param id the appointment to retrieve
     * @return the appointment as a list
     */
    public Appointment getSingleAppointment(int id) {
        return getAppointments(" id = '" + Integer.toString(id) + "' ").get(0);
    }
    
    /**
     * Returns a list of appointments on
     * the given day and time.
     * 
     * @param theDate the date of the appointment
     * @param theTime the time of the appointment
     * @return the list of appointments
     */
    public List<Appointment> getAppointmentByDateTime(int theDate, int theTime) {
        String time = Integer.toString(theTime);
        if (theTime < 1000) {
            time = "0" + time;
        }

        String date = Integer.toString(theDate) + time;
        date = date.substring(0, 4) + '-' + date.substring(4, 6) 
                                    + '-' + date.substring(6, 8)
                                    + ' ' + date.substring(8, 10) 
                                    + ":" + date.substring(10, 12) 
                                    + ":00";
        return getAppointments(" time = '" + date + "' "); 
    }

    /**
     * Returns a list of appointments on
     * the given day.
     * 
     * @param theDate the date of the appointment
     * @return the list of appointments
     */
    public List<Appointment> getAppointmentByDate(int theDate) {
        String date = Integer.toString(theDate);
        date = date.substring(0, 4) + '-' + date.substring(4, 6) 
               + '-' + date.substring(6, 8);

        return getAppointments(" time LIKE '" + date + "%' "); 
    }
    
    /**
     * Returns a list of appointments for the 
     * given patient.
     * 
     * @param pat the patient
     * @return a list of appointments
     */
    public List<Appointment> getAppointmentByPatient(Patient pat) {
        final String id = Integer.toString(pat.getID());
        return getAppointments(" p_id = '" + id + "' ");
    }
    
    /**
     * Returns a list of appointments for the 
     * given nurse.
     * 
     * @param nurse the nurse
     * @return a list of appointments
     */
    public List<Appointment> getAppointmentByNurse(Nurse nurse) {
        final String id = Integer.toString(nurse.getID());
        return getAppointments(" n_id = '" + id + "' ");
    }
    
    /**
     * Returns a list of appointments for the 
     * given doctor.
     * 
     * @param doc the doctor
     * @return a list of appointments
     */
    public List<Appointment> getAppointmentByDoctor(Doctor doc) {
        final String id = Integer.toString(doc.getID());
        return getAppointments(" d_id = '" + id + "' ");
    }
    
    /**
     * Returns all the appointments based on the
     * given condition. Pass in "" to retrieve all 
     * appointments.
     * 
     * @param where the condition to return
     * @return a list of appointments
     */
    private List<Appointment> getAppointments(String where) {
        final List<Appointment> apps = new ArrayList<Appointment>();
        
        final String queryAppointments = "select * from appointments" +
        		                         where + "order by id;";
        final String queryInstructions = "select * from instructions" +
                                         where + "order by id;";
        final String queryLab = "select * from labWork" +
                                         where + "order by id;";
        final String queryPerscriptions = "select * from perscriptions" +
                                         where + "order by id;";
        final String querySymptoms = "select * from symptoms" +
                                         where + "order by id;";
        final String queryVitals = "select * from vitalSigns" +
                                         where + "order by id;";
        
        final ResultSet[] rawdata = this.executeAll(new String[] {queryAppointments,
        queryInstructions, queryLab, queryPerscriptions, querySymptoms, queryVitals});
        
        try {
            for (int i = 1; i < rawdata.length; i++) {
                rawdata[i].next(); // $codepro.audit.disable com.instantiations.assist.eclipse.callNextWithoutHasNext
            }
            while (rawdata[0].next()) {
                
                int id = rawdata[0].getInt("id");
                
                int d_id = rawdata[0].getInt("d_id");
                int p_id = rawdata[0].getInt("p_id");
                int n_id = rawdata[0].getInt("n_id");
                Doctor doc = this.cache.getDoctor(d_id);
                Patient pat = this.cache.getPatient(p_id);
                Nurse nurse = this.cache.getNurse(n_id);
                
                String reason = rawdata[0].getString("reason");
                String diagnosis = rawdata[0].getString("diagnosis");
                
                String rstatus = rawdata[0].getString("status");
                AppointmentStatus status = AppointmentStatus.PENDING; 
                if ("pending".equals(rstatus)) {
                    status = AppointmentStatus.PENDING; 
                }
                else if ("confirmed".equals(rstatus)) {
                    status = AppointmentStatus.CONFIRMED;
                }
                else if ("cancelled".equals(rstatus)) {
                    status = AppointmentStatus.CANCELED;
                }
                
                Date rdate = rawdata[0].getDate("time");
                int date = (rdate.getYear() << 4) | (rdate.getMonth() << 2) 
                                                  | rdate.getDay();
                int time = (rdate.getHours() << 2) | rdate.getMinutes();
                
                List<String> instruc = downloadArray(rawdata[1], id);
                List<String> lab = downloadArray(rawdata[2], id);
                List<String> persc = downloadArray(rawdata[3], id);
                List<String> symp = downloadArray(rawdata[4], id);
                Map<String, Integer> vitals = new HashMap<String, Integer>();
                while (rawdata[5].getInt("id") == id) {
                    vitals.put(rawdata[5].getString("signs"), rawdata[5].getInt("num"));
                    if ((rawdata[5].isLast()) || (!rawdata[5].next())) {
                        break;
                    }
                }
                
                DoctorOrders orders = new DoctorOrders();
                orders.setInstructions(instruc);
                orders.setLabWorkPerformed(lab);
                orders.setPrescription(persc);
                
                TreatmentRecord record = new TreatmentRecord();
                record.setAttendingNurse(nurse);
                record.setDiagnosis(diagnosis);
                record.setSymptoms(symp);
                record.setVitalSigns(vitals);
                record.setOrders(orders);
                
                Appointment app = new Appointment(id, date, time, doc, pat, reason);
                app.setStatus(status);
                app.addRecord(record);
                
                apps.add(app);
                
            }
        } catch (SQLException e) {
            MYLOGGER.severe("Java Dumb has failed: " + e);
        }
        finally {
            this.close();
        }

        return apps; // need to access list of appointments in database
    }
        
    /**
     * Updates an appointment based on id
     * 
     * @param app
     */
    public void updateAppointment(Appointment app) {
        String time = Integer.toString(app.getTime());
        if (app.getTime() < 1000) {
            time = "0" + time;
        }

        String date = Integer.toString(app.getDate()) + time;
        date = date.substring(0, 4) + '-' + date.substring(4, 6) 
                                    + '-' + date.substring(6, 8)
                                    + ' ' + date.substring(8, 10) 
                                    + ":" + date.substring(10, 12) 
                                    + ":00";
        
        String query = "update appointments set d_id = " 
                        + Integer.toString(app.getDoc().getID()) + ", p_id = "
                        + Integer.toString(app.getPat().getID()) + ", time = '"
                        + date + "', reason = '" + app.getReason() + "', status = '"
                        + app.getStatus() + "'";
        final String end = " where id = " + Integer.toString(app.getID()) + ";";
        
        final Nurse nurse = app.getRecord().getAttendingNurse();
        if (null != nurse) {
            query = query + ", n_id = " + Integer.toString(nurse.getID());
        }
        final String diag = app.getRecord().getDiagnosis();
        if (null != diag) {
            query = query + ", diagnosis = '" + diag + "'";
        } 
        query = query + end;
        System.out.println(query);
        this.execute(query);
        this.close();
    }
    
    /**
     * Delete's an appointment based on id
     * 
     * @param app the appointment to delete in the database
     */
    public void deleteAppointment(Appointment app) {
        final int id = app.getID();
        final String query = "delete from appointments where id =" 
                                       + Integer.toString(id) + ';';
        this.execute(query);
        
        this.close();
    }
    //==============================================================================   
    //=============================END APPOINTMENTS=================================
    //==============================================================================
    
    //==============================================================================   
    //===========================CRUD TREATMENT RECORDS=============================
    //==============================================================================
    /**
     * Generates the statement to add a record into the database.
     * 
     * @param record the record to add
     * @param id of the appointment
     */
    public void addTreatmentRecord(TreatmentRecord record, int id) {
        final int offset = 1;
        final List<String> symptoms = record.getSymptoms();
        final Map<String, Integer> vitals = record.getVitalSigns();
        final String[] queries = new String[offset + symptoms.size() + vitals.size()];
        
        final String queryApp = "update appointments set";
        final String where = "where id = " + Integer.toString(id) + ";";
        
        String n_id = "";
        final Nurse nurse = record.getAttendingNurse();
        if (null != nurse) {
            n_id = " n_id = " + Integer.toString(nurse.getID()) + "";
        }
        
        String diag = "";
        final String diagnosis = record.getDiagnosis();
        if (null != diagnosis) {
            diag = " diagnosis = '" + diagnosis + "' ";
        }
        
        if ((!diag.equals("")) && (!n_id.equals(""))) {
            n_id += ",";
        }
        else if ((diag.equals("")) && (!n_id.equals(""))){
            n_id += " ";
        }
        
        queries[0] = queryApp + n_id + diag + where;
        
        final String querySymp = "insert into symptoms (id, data) values (" 
                           + Integer.toString(id) + ", '";
        for(int i = 0; i < symptoms.size(); i++) {
            queries[offset + i] = querySymp + symptoms.get(i) + "');";
        }
        
        final String queryVital = "insert into vitalSigns (id, signs, num) values ("
                            + Integer.toString(id) + ", '";
        final Iterator<String> signs = vitals.keySet().iterator();
        int index = offset + symptoms.size();
        while(signs.hasNext()) { // $codepro.audit.disable com.instantiations.assist.eclipse.analysis.audit.rule.effectivejava.minimizeScopeOfLocalVariables
            String sign = signs.next();
            int num = vitals.get(sign);
            queries[index] = queryVital + sign + "', " + Integer.toString(num) + ");";
            index++;
        }
        
        this.executeAll(queries);
        this.close();
        //this.addDoctorOrders(record.getOrders(), id);
        
    }
        
    // TTTTTTTHHHHHHISSSSSS NOT DDDDDDOOOOOOOONNNNNEEEEEEEEE
    /**
     * Gets the record based on the appointment id
     * 
     * @param id of the appointment
     * @return the treatment record
     */
    public TreatmentRecord getRecord(int id) {
        final int offset = 4;
        final String[] queries = new String[offset];
        
        queries[0] = "select n_id, diagnosis from appointments where id = "
            + Integer.toString(id) + ";";
        queries[1] = "select data from instructions where id = " 
            + Integer.toString(id) + ";";
        queries[2] = "select data from perscriptions where id = " 
            + Integer.toString(id) + ";";
        queries[3] = "select data from labWork where id = " + Integer.toString(id) + ";";
        
        final ResultSet[] rawdata = this.executeAll(queries);
        final TreatmentRecord record = null;
        try {
            rawdata[0].next();
        } catch (SQLException e) {
            
            MYLOGGER.severe("Java Dump Failed" + e);
        }
        
        return record;
    }
    
    /**
     * Updates the treatment record data on the database for the
     * given id of the treatment record
     * 
     * @param record
     * @param id
     */
    public void updateRecord(TreatmentRecord record, int id) {
        final int offset = 3;
        final List<String> symptoms = record.getSymptoms();
        final Map<String, Integer> vitals = record.getVitalSigns();
        final String[] queries = new String[offset + symptoms.size() + vitals.size()];
        
        String uApp = "update appointments set";
        final String where = "where id = " + Integer.toString(id) + ";";
        
        final String diag = record.getDiagnosis();
        final Nurse nurse = record.getAttendingNurse();
        
        if (null != diag) {
            uApp += " diagnosis = '" + diag + "',"; 
        }
        if (null != nurse) {
            uApp += " n_id = " + Integer.toString(nurse.getID()) + " ";
        }
        else {
            uApp = uApp.substring(0, uApp.length() - 1) + " ";
        }
        
        queries[0] = uApp + where;
        queries[1] = "delete from symptoms " + where;
        queries[2] = "delete from vitalSigns " + where;
        
        final String querySymp = "insert into symptoms (id, data) values (" 
                                    + Integer.toString(id) + ", '";
        for(int i = 0; i < symptoms.size(); i++) {
            queries[offset + i] = querySymp + symptoms.get(i) + "');";
        }
        
        final String queryVital = "insert into vitalSigns (id, signs, num) values ("
                                    + Integer.toString(id) + ", '";
        final Iterator<String> signs = vitals.keySet().iterator();
        int index = offset + symptoms.size();
        while(signs.hasNext()) { // $codepro.audit.disable com.instantiations.assist.eclipse.analysis.audit.rule.effectivejava.minimizeScopeOfLocalVariables
            String sign = signs.next();
            int num = vitals.get(sign);
            queries[index] = queryVital + sign + "', " + Integer.toString(num) + ");";
            index++;
        }
        
        this.executeAll(queries);
        this.close();
    }
    
    /**
     * Deletes Treatment Record data based on id
     * 
     * @param id the id to delete
     */
    public void deleteRecord(int id){
        final String[] queries = new String[3];
        queries[0] = "delete from symptoms where id = " + Integer.toString(id) + ";";
        queries[1] = "delete from vitalSigns where id = " + Integer.toString(id) + ";";
        queries[2] = "update appointments set n_id = null, diagnosis = null where id = " 
                        + Integer.toString(id) + ";";
        this.executeAll(queries);
        this.close();
        
    }
    //==============================================================================   
    //============================END TREATMENT RECORDS=============================
    //==============================================================================  
    
    //==============================================================================   
    //=============================CUD DOCTORS ORDERS===============================
    //==============================================================================
    /**
     * Generates the queries to add doctors orders into 
     * the database for the appointment id
     * 
     * @param orders the doctors orders to add
     * @param id of the appointment
     */
    public void addDoctorOrders(DoctorOrders orders, int id) {
        final List<String> instruc = orders.getInstructions();
        final List<String> lab = orders.getLabWorkPerformed();
        final List<String> persc = orders.getPrescription();
        
        final String[] queries = new String[instruc.size() + lab.size() + persc.size()];
        
        final String queryI = "insert into instructions (id, data) values (" 
                        + Integer.toString(id) + ", '";
        for(int i = 0; i < instruc.size(); i++) {
            queries[i] = queryI + instruc.get(i) + "');";
        }
        
        final String queryL = "insert into labWork (id, data) values (" 
                        + Integer.toString(id) + ", '";
        for(int i = 0; i < lab.size(); i++) {
            queries[i + instruc.size()] = queryL + lab.get(i) + "');";
        }
        
        final String queryP = "insert into perscriptions (id, data) values (" 
            + Integer.toString(id) + ", '";
        for(int i = 0; i < persc.size(); i++) {
            queries[i + instruc.size() + lab.size()] = queryP + persc.get(i) + "');";
        }
        
        this.executeAll(queries);
        this.close();
        
    }
    
    /**
     * Update the doctors orders on the database
     * 
     * @param orders new orders
     * @param id id of the orders
     */
    public void updateDoctorOrders(DoctorOrders orders, int id) {
        this.deleteDoctorOrders(id);
        this.addDoctorOrders(orders, id);
    }
    
    /**
     * Delete orders based on id.
     * 
     * @param id
     */
    public void deleteDoctorOrders(int id) {
        final int offset = 3;
        final String[] queries = new String[offset];
        
        queries[0] = "delete from instructions where id = " + Integer.toString(id) + ";";
        queries[1] = "delete from labWork where id = " + Integer.toString(id) + ";";
        queries[2] = "delete from perscriptions where id = " + Integer.toString(id) + ";";
        
        this.executeAll(queries);
        this.close();
    }
    //==============================================================================   
    //=============================END DOCTORS ORDERS===============================
    //==============================================================================  
    
    //==============================================================================  
    //===========================LOGIN FUNCTIONALITY================================
    //==============================================================================
    /**
     * Verifies the user is has a valid username and password.
     * The method returns a list of strings that consist of several
     * key pieces of data: the first string will be "true" or "false"
     * depending on if the login verification was successful. If it
     * was succesful, the second value is a string of the id and
     * the third value is a string of the rank (patient, nurse, 
     * doctor, or sys admin). If unsuccessful, the second value is
     * the number of current attempts if aplicable (-1 if not) and
     * and the third value is a string value of the type of error.
     * attempt = too many attempts
     * password = invalid password
     * inactive = user is pending aceptance from admin
     * username = invalid username
     * 
     * 
     * @param username the username
     * @param password the password
     * 
     * @return information about the attempt
     */
    public String[] login(String username, String password) {
        final String[] fin = new String[3];
        String query = "select id, pword, active, attempts, rank from " +
        		"users where username = '" + username + "';";
        final ResultSet answer = this.execute(query);
        try {
            answer.next();
            final int id = answer.getInt("id");
            final String pword = answer.getString("pword");
            final boolean active = answer.getBoolean("active");
            final int attempts = answer.getInt("attempts");
            final String rank = answer.getString("rank");
            
            this.close();
            
            if ((password.equals(pword)) && (active)) {
                if (3 > attempts) {
                    fin[0] = "true";
                    fin[1] = Integer.toString(id);
                    fin[2] = rank;
                }
                else {
                    fin[0] = "false";
                    fin[1] = Integer.toString(attempts);
                    fin[2] = "attempt";
                    
                }  
            }
            else if (active) {
                if (3 > attempts) {
                    fin[0] = "false";
                    fin[1] = Integer.toString(attempts + 1);
                    fin[2] = "password";
                
                    query = "update users set attempts = '" 
                                + Integer.toString(attempts + 1)
                                + "' where username = '" + username + "';";
                    this.execute(query);
                    this.close();
                }
                else {
                    fin[0] = "false";
                    fin[1] = Integer.toString(attempts);
                    fin[2] = "attempt";
                }
            }
            else {
                fin[0] = "false";
                fin[1] = "-1";
                fin[2] = "inactive";
            }
            
        }
        catch (Exception e) {
            MYLOGGER.severe("Exception: " + e.getMessage());
            fin[0] = "false";
            fin[1] = "-1";
            fin[2] = "username";
        }
        
        return fin;
    }
    
    /**
     * Adds the username into the database
     * 
     * @param username the username to add
     * @param password the password to set
     * @param rank the desired rank
     * @return true if successful, false if not
     */
    public boolean canRegister(String username, String password, String rank) {
        boolean success = true;
        final String available = "select username from users where username = '" 
                            + username + "';";
        final ResultSet answer = this.execute(available);
        try {
            if (answer.next()) {
                success = false;
                this.close();
            }
            else {
                this.close();
                final String sql = "insert into users (username, pword, rank) values ('"
                                + username + "', '" + password + "', '" + rank 
                                + "');";
                this.execute(sql);
            }
        } catch (SQLException e) {
            MYLOGGER.severe("sql dun goofed\n " + e);
        } catch (Exception e) {
            MYLOGGER.severe("Connection error" + e);
        }
        
        return success;
    }
    //==============================================================================   
    //===========================END LOGIN FUNCTIONALITY============================
    //==============================================================================    

    //==============================================================================   
    //============================MISC HELPER FUNCTIONS=============================
    //==============================================================================  
    /**
     * Converts rows of data into an array based 
     * on id
     * 
     * @param data table of data
     * @param id to compare
     * @return the list of string data
     */
    private List<String> downloadArray(ResultSet data, int id) {
        final List<String> fin = new ArrayList<String>();
        try {
            while (data.getInt("id") == id) {
                fin.add(data.getString("data"));
                if ((data.isLast()) || (!data.next())) {
                    break;
                }
                
            }
        } catch (SQLException e) {
            MYLOGGER.severe("Java Dumb has failed" + e);
        }
        return fin;
    }
    
    /**
     * Fixes audit rules XD.
     * 
     * @return the string
     */
    @Override
    public String toString() {
        return "Connection to the database!";
    }
    //==============================================================================   
    //============================END HELPER FUNCTIONS==============================
    //==============================================================================  


}
